package com.rjj.wlqq.tool.excel.excelOne;


import com.rjj.wlqq.tool.excel.interfaces.IExcel;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.List;
import java.util.Map;

/**
 * 把给出的list数据写入到excel中下载
 * 这里数据类型只用到了jfinal的Record。
 * 可以更具自己的需求来扩展
 */
public class ExcelExportUtil
{

    /**
     * 工作薄对象
     */
    private Workbook wb;
    private Sheet sheet;
    private boolean isSXSSFWorkbook = false;
    /**
     * Excel sheet最大行数，默认65536
     */
    public static final int sheetSize = 65536;


    /**
     * 私有构造初始化Workbook对象
     */
    private ExcelExportUtil()
    {
        createWorkbook();
    }

    /**
     * 类级的内部类，也就是静态的成员式内部类，该内部类的实例与外部类的实例
     * 没有绑定关系，而且只有被调用到才会装载，从而实现了延迟加载
     */
    private static class SingletonHolder
    {
        /**
         * 静态初始化器，由JVM来保证线程安全
         */
        private static final ExcelExportUtil INSTANCE = new ExcelExportUtil();
    }

    /**
     * 对外提供使用
     */
    public static ExcelExportUtil getInstance()
    {
        return SingletonHolder.INSTANCE;
    }

    /**
     * 初始化Workbook对象
     */
    public void createWorkbook()
    {
        if (isSXSSFWorkbook)
        {
            this.wb = new SXSSFWorkbook(500); // 【SXSSFWorkbook】
        } else
        {
            this.wb = new HSSFWorkbook(); //【HSSFWorkbook】
        }
    }


    /**
     * 新方法，用了接口
     *
     * @param response
     * @param request
     * @param filename   导出的文件名
     * @param sheetDatas 产生一个sheet需要的数据,数组的形式可以导出多个sheet页
     *                   <p>
     *                   *要求：书写形式List<Pair> titles, List<List<Map<String,Object>>> mapList 中Pair和map的key相同
     *                   *new Pair("a","标题")  ，new Record().put("a","数据")
     */
    public void exportByMap(HttpServletResponse response, HttpServletRequest request, String filename, IExcel excel, SheetData... sheetDatas)
    {

        //sheet的下标
        int sheetIndex = 0;
        int sheetNameIndex = 0;

        //多张sheet,规定数据使用sheet,名字
        for (SheetData sheetData : sheetDatas)
        {

            List<Pair> titles = sheetData.titles;
            List<Map<String, Object>> records = sheetData.records;


            //计算几个sheet，数据超出限制，新建sheet
            double sheetNo = Math.ceil(records.size() * 1D / sheetSize);
            for (int i = 0; i < sheetNo; i++)
            {
                //创建sheet
                sheetIndex = createSheet(excel, sheetNameIndex, sheetIndex);

                createTitle(titles);
                fillExcelData(excel, titles, records, i);
            }

            sheetNameIndex++;
        }

        ExcelWriteStream.writeStream(filename, wb, response, request);
    }

    /**
     * 填充excel主体内容数据
     *
     * @param excel   接口过滤字段
     * @param titles  表头数据
     * @param records 主体数据
     * @param index   一个数据主，划分为几个sheet
     */
    private void fillExcelData(IExcel excel, List<Pair> titles, List<Map<String, Object>> records, int index)
    {

        int startNo = index * sheetSize;
        int endNo = Math.min(startNo + sheetSize, records.size());

        //从哪一行开始创建数据
        int rowIndex = 1;
        CellStyle cellStyle = createContentStyle(wb);
        Row row;

        //处理每一行
        for (int i = startNo; i < endNo; i++)
        {
            Map<String, Object> record = records.get(i);

            row = sheet.createRow(rowIndex);
            row.setHeight((short) 450);
            rowIndex++;

            for (int j = 0; j < titles.size(); j++)
            {
                final Pair pair = titles.get(j);
                Cell cell = row.createCell(j);
                cell.setCellStyle(cellStyle);
                setAutoSizeColumn(j);

                Object value = record.get(pair.column);
                //如果不实现接口，让接口为 null  那么默认使用直接都为字符串的形式
                if (excel == null)
                {
                    cell.setCellValue(value == null ? "" : value.toString());
                } else
                {
                    //过滤值
                    cell.setCellValue(excel.filterValue(pair.column, value == null ? "" : value.toString()));
                }
            }
        }
    }

    /**
     * 创建excel表头
     *
     * @param titles title数据
     */
    private void createTitle(List<Pair> titles)
    {
        final CellStyle cellStyle = createTitleStyle(wb);
        //创建标题行，下标=0开始
        Row row = sheet.createRow(0);
        row.setHeight((short) 450);

        for (int i = 0; i < titles.size(); i++)
        {
            final Pair pair = titles.get(i);
            Cell cell = row.createCell(i);
            cell.setCellStyle(cellStyle);
            setAutoSizeColumn(i);

            cell.setCellValue(pair.title);
        }
    }

    /**
     * 手动设置单元格宽度【SXSSFWorkbook】
     */
    public void setAutoSizeColumn(int column)
    {
        if (wb instanceof SXSSFWorkbook)
        {
            int width = Math.max(15 * 256, Math.min(255 * 256, sheet.getColumnWidth(column) * 12 / 10));
            sheet.setColumnWidth(column, width);
        }
    }

    /**
     * 创建sheet
     * 如果使用HSSFWorkbook 打开注释部分，否则关闭
     *
     * @param excel      接口
     * @param sheetIndex sheet下标
     * @return
     */
    private int createSheet(IExcel excel, int sheetNameIndex, int sheetIndex)
    {
        sheet = wb.createSheet();
        sheet.setDefaultColumnWidth(20);
        sheet.setDefaultRowHeight((short) 450);
        if (wb instanceof HSSFWorkbook)
        {
            sheet.autoSizeColumn(0);//SXSSFWorkbook 不能用,HSSFWorkbook可以使用 【HSSFWorkbook】
        }

        //设置sheet的名字
        String sheetName = getSheetName(excel, sheetNameIndex);
        sheetName = sheetNameIndex == sheetIndex ? sheetName : sheetName + sheetIndex;

        wb.setSheetName(sheetIndex, sheetName);
        sheetIndex++;
        return sheetIndex;
    }


    /**
     * @param response
     * @param request
     * @param filename 导出的文件名
     * @param titles   标题列和列名的对应.column:列名,title标题名
     * @param records  记录
     */
    @Deprecated
    public static void exportByRecord(HttpServletResponse response, HttpServletRequest request, String filename, List<Pair> titles, List<Map<String, Object>> records)
    {
        exportByMapOld(response, request, filename, new SheetData(titles, records));
    }


    /**
     * 旧方法可以参看
     *
     * @param response
     * @param request
     * @param filename   导出的文件名
     * @param sheetDatas 产生一个sheet需要的数据
     */
    @Deprecated
    public static void exportByMapOld(HttpServletResponse response, HttpServletRequest request, String filename, SheetData... sheetDatas)
    {

        HSSFWorkbook wb = new HSSFWorkbook();
        CellStyle titleCellStyle = createTitleStyle(wb);
        CellStyle cellStyle = createContentStyle(wb);

        //多张sheet
        for (SheetData sheetData : sheetDatas)
        {
            List<Pair> titles = sheetData.titles;

            List<Map<String, Object>> records = sheetData.records;

            HSSFSheet sheet = wb.createSheet();
            sheet.setDefaultColumnWidth(20);
            sheet.setDefaultRowHeight((short) 450);
            sheet.autoSizeColumn(0);
            int rowIndex = 0, cellIndex = 0;

            //创建标题行
            HSSFRow row = sheet.createRow(rowIndex);
            row.setHeight((short) 450);
            rowIndex++;

            for (Pair pair : titles)
            {

                HSSFCell cell = row.createCell(cellIndex);
                cell.setCellStyle(titleCellStyle);
                cellIndex++;

                cell.setCellValue(pair.title);
            }

            String str = "";
            //处理每一行
            for (Map<String, Object> record : records)
            {

                row = sheet.createRow(rowIndex);
                row.setHeight((short) 450);
                rowIndex++;
                cellIndex = 0;

                for (Pair pair : titles)
                {

                    HSSFCell cell = row.createCell(cellIndex);
                    cell.setCellStyle(cellStyle);
                    //设置样式
                    cellIndex++;
                    str = pair.column.trim();
                    Object value = record.get(str);
                    if (value != null)
                    {
                        cell.setCellValue(value.toString());
                    }
                }
            }
        }

        ExcelWriteStream.writeStream(filename, wb, response, request);
    }


    /**
     * 内容样式
     */
    private static CellStyle createContentStyle(Workbook wb)
    {
        //内容行的style
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setWrapText(true);
        Font font2 = wb.createFont();
        font2.setFontName("微软雅黑");
        cellStyle.setFont(font2);
        return cellStyle;
    }

    /**
     * 标题行的style
     */
    private static CellStyle createTitleStyle(Workbook wb)
    {
        CellStyle titleCellStyle = wb.createCellStyle();
        titleCellStyle.setAlignment(HorizontalAlignment.CENTER);
        //自动换行
        titleCellStyle.setWrapText(true);
        Font font = wb.createFont();
        font.setBold(true);
        font.setFontName("微软雅黑");
        titleCellStyle.setFont(font);
        return titleCellStyle;
    }


    /**
     * 获取sheetName的名称
     *
     * @param excel      接口
     * @param sheetIndex sheet下标，几个sheetName
     */
    private static String getSheetName(IExcel excel, int sheetIndex)
    {
        String sheetName;
        if (excel == null)
        {
            sheetName = "sheet" + sheetIndex;
        } else
        {
            String[] setSheetNames = excel.setSheetName();
            sheetName = setSheetNames == null ? "sheet" + sheetIndex : setSheetNames.length <= 0 ? "sheet" + sheetIndex : setSheetNames[sheetIndex];
        }
        return sheetName;
    }

}
